import sqlite3

def modify_database():
    # 连接到数据库
    conn = sqlite3.connect('trade_monitor.db')
    cursor = conn.cursor()

    try:
        # 获取旧表的结构
        cursor.execute("PRAGMA table_info(products)")
        columns = cursor.fetchall()

        # 找到 is_monitored 字段的位置
        is_monitored_index = None
        for index, column in enumerate(columns):
            if column[1] == 'is_monitored':
                is_monitored_index = index
                break

        if is_monitored_index is None:
            print("未找到 is_monitored 字段")
            return

        # 生成新的表结构
        new_columns = []
        for i in range(len(columns)):
            if i == is_monitored_index:
                new_columns.append('限额止损 REAL DEFAULT 3000')
            new_columns.append(f"{columns[i][1]} {columns[i][2]}")

        # 创建新表
        new_table_sql = f'CREATE TABLE new_table ({", ".join(new_columns)})'
        cursor.execute(new_table_sql)

        # 迁移数据
        old_columns = [column[1] for column in columns]
        new_columns = [col.split(' ')[0] for col in new_columns]
        insert_sql = f'INSERT INTO new_table ({", ".join(new_columns)}) SELECT {", ".join(old_columns)}, 3000 FROM products'
        cursor.execute(insert_sql)

        # 删除旧表
        cursor.execute('DROP TABLE products')

        # 重命名新表
        cursor.execute('ALTER TABLE new_table RENAME TO products')

        # 提交更改
        conn.commit()
        print("数据库修改成功")

    except Exception as e:
        print(f"发生错误: {e}")
        conn.rollback()
    finally:
        # 关闭连接
        conn.close()

if __name__ == "__main__":
    modify_database()
